from django.http import HttpResponse, JsonResponse
from django.http import FileResponse
from django.views.decorators.http import require_http_methods
import base64, json
from ..models import Record
from urllib.parse import urlencode
import threading
from .confs.conf import get_production_conn
from openpyxl import Workbook
from io import BytesIO

def_response_o = {
    "code": 0,
    "result": "",
    "message": "",
    "type": 'success',
  }

user_info_def =  {
    "userId": '1',
    "username": 'admin',
    "realName": 'Gentlebands.admin',
    "avatar": '',
    "desc": 'manager',
    # "password": '123456',
    # "token": 'fakeToken1',
    "roles": [
    {
        "roleName": 'Super Admin',
        "value": 'super',
    },
    ],
}


counts = """SELECT COUNT(*) """
select = '''SELECT
    pp.order_id AS 'orderId',
    ( SELECT ps.post_status FROM wp_posts ps WHERE pp.order_id = ps.id ) AS 'status',
    ( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_order_total' ) AS 'total',
    ( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.product_id AND p.meta_key = '_sku' ) AS 'sku',
    ( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_billing_first_name' ) AS 'fn',
    ( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_billing_last_name' ) AS 'ln',
    ( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_billing_email' ) AS 'email',
    ( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_billing_address_1' ) AS 'billingAddress',
	( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_billing_city' ) AS 'billingCity',
	( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_billing_postcode' ) AS 'billingZipcode',
	( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_billing_country' ) AS 'billingCountry',
	( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_billing_state' ) AS 'billingState',
	( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_shipping_address_1' ) AS 'shippingAddress',
	( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_shipping_city' ) AS 'shippingCity',
	( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_shipping_postcode' ) AS 'shippingZipcode',
	( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_shipping_country' ) AS 'shippingCountry',
	( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_shipping_state' ) AS 'shippingState',
	( SELECT p.meta_value FROM wp_postmeta p WHERE p.post_id = pp.order_id AND p.meta_key = '_wc_method_id' ) AS 'shippingMethod',
    ( SELECT ps.post_title FROM wp_posts ps WHERE pp.product_id = ps.id ) AS 'productName',
    ( 
        SELECT post_title FROM wp_wc_order_coupon_lookup AS cp
        LEFT JOIN ( SELECT post_title, id FROM wp_posts) AS ps 
        ON (ps.id = cp.`coupon_id`)
        WHERE cp.`order_id` = pp.`order_id`
    ) AS 'coupon',
    pp.product_qty AS 'quantity',
    pp.date_created AS 'date' '''
limit = ""
froms = "FROM  wp_wc_order_product_lookup pp"

@require_http_methods(["GET"])
def order_order_sku(request):
    global counts, select, limit, froms
    params = request.GET
    re = json.loads(json.dumps(def_response_o))
    db_conn = get_production_conn()
    start = params['start'] if 'start' in params.keys() else ''
    end = params['end'] if 'end' in params.keys() else ''
    if not start or not end:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "请指定起止日期"
        return JsonResponse(re)
    start = start.split(' ')[0]
    end = end.split(' ')[0]
    where = f"WHERE  pp.date_created >= '{start} 00:00:00' AND pp.date_created <= '{end} 23:59:59'"
    sql = f'''{select}
    {froms} 
    {where}
    {limit};'''
    # total = db_conn.query_one(f"{counts} {froms} {where}")
    print(sql)
    result = db_conn.query_all(sql)
    re['result'] = result
    return JsonResponse(re)

@require_http_methods(["GET"])
def down_order_sku_excel(request):
    global counts, select, limit, froms
    params = request.GET
    re = json.loads(json.dumps(def_response_o))
    db_conn = get_production_conn()
    start = params['start'] if 'start' in params.keys() else ''
    end = params['end'] if 'end' in params.keys() else ''
    if not start or not end:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "请指定起止日期"
        return JsonResponse(re)
    start = start.split(' ')[0]
    end = end.split(' ')[0]
    where = f"WHERE  pp.date_created >= '{start} 00:00:00' AND pp.date_created <= '{end} 23:59:59'"
    sql = f'''{select}
    {froms} 
    {where}
    {limit};'''
    # total = db_conn.query_one(f"{counts} {froms} {where}")
    result = db_conn.query_all(sql)

    title_name = f'{start}-to-{end}'
        # 创建一个Excel工作簿
    excel_file = Workbook()
    sheet = excel_file.active
    sheet.title = title_name

    sheet[f'A1'] = "订单ID"
    sheet[f'B1'] = "状态"
    sheet[f'C1'] = "合计"
    sheet[f'D1'] = "SKU"
    sheet[f'E1'] = "ProductName"
    sheet[f'F1'] = "数量"
    sheet[f'G1'] = "FN"
    sheet[f'H1'] = "LN"
    sheet[f'I1'] = "Email"
    sheet[f'J1'] = "Coupon"
    sheet[f'K1'] = "日期"
    sheet[f'L1'] = "Billing Address"
    sheet[f'M1'] = "Billing City"
    sheet[f'N1'] = "Billing Zipcode"
    sheet[f'O1'] = "Billing Country"
    sheet[f'P1'] = "Billing State"
    sheet[f'Q1'] = "Shipping Address"
    sheet[f'R1'] = "Shipping City"
    sheet[f'S1'] = "Shipping Zipcode"
    sheet[f'T1'] = "Shipping Country"
    sheet[f'U1'] = "Shipping State"
    sheet[f'V1'] = "Shipping Method"
    sheet[f'W1'] = "日期"

    for i in range(len(result)):
        no = i+1
        # 添加一些数据
        sheet[f'A{no+1}'] = result[i]['orderId']
        sheet[f'B{no+1}'] = result[i]['status']
        sheet[f'C{no+1}'] = result[i]['total']
        sheet[f'D{no+1}'] = result[i]['sku']
        sheet[f'E{no+1}'] = result[i]['productName']
        sheet[f'F{no+1}'] = result[i]['quantity']
        sheet[f'G{no+1}'] = result[i]['fn']
        sheet[f'H{no+1}'] = result[i]['ln']
        sheet[f'I{no+1}'] = result[i]['email']
        sheet[f'J{no+1}'] = result[i]['coupon']
        sheet[f'K{no+1}'] = result[i]['date']
        sheet[f'L{no+1}'] = result[i]['billingAddress']
        sheet[f'M{no+1}'] = result[i]['billingCity']
        sheet[f'K{no+1}'] = result[i]['billingZipcode']
        sheet[f'O{no+1}'] = result[i]['billingCountry']
        sheet[f'P{no+1}'] = result[i]['billingState']
        sheet[f'Q{no+1}'] = result[i]['shippingAddress']
        sheet[f'R{no+1}'] = result[i]['shippingCity']
        sheet[f'S{no+1}'] = result[i]['shippingZipcode']
        sheet[f'T{no+1}'] = result[i]['shippingCountry']
        sheet[f'U{no+1}'] = result[i]['shippingState']
        sheet[f'V{no+1}'] = result[i]['shippingMethod']
        sheet[f'W{no+1}'] = result[i]['date']

    # 保存工作簿到内存中的BytesIO
    excel_bytes_io = BytesIO()
    excel_file.save(excel_bytes_io)
    excel_bytes_io.seek(0)
    
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = f'attachment; filename={title_name}'
    response.write(excel_bytes_io.getvalue())
    return response

    # # 设置响应头
    # response = make_response(excel_bytes_io.getvalue())
    # response.headers['Content-Disposition'] = f"attachment; filename=example.xlsx"
    # response.headers['Content-Type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    
    # # 返回响应
    # return response